﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using ERPStudio;
using System.Globalization;

namespace Amix
{
    public partial class FrmExcel : FrmRezumat
    {
        private String XlsConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"Selectie.xls\";Extended Properties=\"" + "Excel 8.0;HDR=NO;\"";
        public FrmExcel(String fName,ModuleClass mc):base(mc)
        {
            InitializeComponent();
            FileName = fName;
        }

        public String FileName=null;
        DataSet ds = null;
        private void FrmExcel_Load(object sender, EventArgs e)
        {
            SqlConnection con = DeschideConexiune(workConnString);
            this.unitatiMasuraTableAdapter.Connection = con;
            this.unitatiMasuraTableAdapter.Fill(this.eSRPDataSet.UnitatiMasura);
            departamenteTableAdapter.Connection = con;
            departamenteTableAdapter.Fill(this.eSRPDataSet.Departamente, ModuleInfo.CodUnitate);
            InchideConexiune(con);

            if (FileName == null|| FileName==String.Empty)
            {
                if(openFileDialogXls.ShowDialog()!= System.Windows.Forms.DialogResult.OK)
                    return;
                FileName = openFileDialogXls.FileName;
            }

            DataTable dt = null;
            String ConnString = XlsConnection;
            ConnString = ConnString.Replace("\"Selectie.xls\"", "\"" + FileName + "\"");
            OleDbConnection oledbConn = new OleDbConnection(ConnString);

            try
            {
                // Open connection
                oledbConn.Open();
                dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    this.listBox1.Items.Add(row["TABLE_NAME"].ToString());
                    i++;
                }

                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }


                // Create OleDbCommand object and select data from worksheet Sheet1
                /*OleDbCommand cmd = new OleDbCommand(
                    "SELECT * FROM [Sheet1$]"
                  , oledbConn);

                // Create new OleDbDataAdapter 
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "MainDataTable");

                int cate = 0;
                foreach (DataRow rd in ds.Tables[0].Rows)
                {
                    rd1 = rd;
                    MyTace(rd["Ad Group"].ToString());
                    cate++;
                }*/
            }
            catch (Exception ex)
            {
                    throw (ex);
            }
            finally
            {
                oledbConn.Close();
                oledbConn.Dispose();
            }
      
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (FileName == null)
                return;
            String ConnString = XlsConnection;
            ConnString = ConnString.Replace("\"Selectie.xls\"", "\"" + FileName + "\"");
            OleDbConnection oledbConn = new OleDbConnection(ConnString);
            try
            {
                // Open connection
                oledbConn.Open();

                //OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + this.listBox1.SelectedItem.ToString() + "]", oledbConn);

                

                OleDbCommand cmd = new OleDbCommand(
                    "SELECT * FROM [" + this.listBox1.SelectedItem.ToString() + "]"
                  , oledbConn);

                // Create new OleDbDataAdapter 
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                oleda.Fill(ds, this.listBox1.SelectedItem.ToString());
                bindingSource1.DataSource = ds;
                bindingSource1.DataMember = this.listBox1.SelectedItem.ToString();

                this.dataGridView1.DataSource = bindingSource1;
                this.dataGridView1.AutoGenerateColumns = true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                oledbConn.Close();
                oledbConn.Dispose();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (FileName == null)
                return;
            String ConnString = XlsConnection;
            ConnString = ConnString.Replace("\"Selectie.xls\"", "\"" + FileName + "\"");
            OleDbConnection oledbConn = new OleDbConnection(ConnString);
            try
            {
                // Open connection
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand(
                     "SELECT * FROM [" + this.listBox1.SelectedItem.ToString() + "]"
                   , oledbConn);

                // Create new OleDbDataAdapter 
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, this.listBox1.SelectedItem.ToString());
                bindingSource1.DataSource = ds;
                bindingSource1.DataMember = this.listBox1.SelectedItem.ToString();

                this.dataGridView1.DataSource = bindingSource1;
                this.dataGridView1.AutoGenerateColumns = true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                oledbConn.Close();
                oledbConn.Dispose();
            }
        }

        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            this.textBox1.Text = e.RowIndex.ToString();
            this.textBox2.Text =
                ds.Tables[this.listBox1.SelectedItem.ToString()].Columns[e.ColumnIndex].ColumnName.ToString();
            this.textBox3.Text = ds.Tables[this.listBox1.SelectedItem.ToString()].Rows[e.RowIndex][e.ColumnIndex].ToString();
            this.label4.Text = "";
            foreach (DataColumn dc in ds.Tables[this.listBox1.SelectedItem.ToString()].PrimaryKey)
                this.label4.Text += dc.ToString() + ";";
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            if (FileName == null)
                return;
            String ConnString = XlsConnection;
            ConnString = ConnString.Replace("\"Selectie.xls\"", "\"" + FileName + "\"");
            OleDbConnection oledbConn = new OleDbConnection(ConnString);
            SqlCommand cmdInsert = new SqlCommand("", DeschideConexiune(workConnString));

            cmdInsert.CommandText = "DELETE FROM [AmixRegistru] WHERE [Compus] IN (3,4)";
            DataRowView drs = null;
            try
            {
                // Open connection

                if (stergeCB.Checked)
                    cmdInsert.ExecuteNonQuery();
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand(
                     "SELECT * FROM [" + this.listBox1.SelectedItem.ToString() + "]"
                   , oledbConn);

                // Create new OleDbDataAdapter 
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, this.listBox1.SelectedItem.ToString());
                bindingSource1.DataSource = ds;
                bindingSource1.DataMember = this.listBox1.SelectedItem.ToString();

                //this.dataGridView1.DataSource = bindingSource1;
                //this.dataGridView1.AutoGenerateColumns = true;

                progressBar1.Maximum = bindingSource1.Count;
                progressBar1.Minimum = 0;
                int i = 0;
                
                foreach (DataRowView dr in bindingSource1)
                {
                    drs=dr;
                    progressBar1.Value = i++;
                    //MyTrace("F2");
                    String  codn = dr["F2"].ToString().Trim();
                    if (codn.Trim() == String.Empty)
                    {
                        MyTrace(dr["F1"].ToString() + " -  obiect fara numar de inventar");
                        continue;
                    }
                    //MyTrace("F1");
                    String den = dr["F1"].ToString().Trim();
                    //MyTrace("F4" + dr["F2"].ToString());
                    Decimal stoc_fin = Decimal.Zero;
                    if(dr["F4"].ToString().Trim()!=String.Empty)
                        stoc_fin = Convert.ToDecimal(dr["F4"]);
                    //MyTrace("F3");
                    String DenUM = dr["F3"].ToString();
                    //MyTrace("F6");
                    Int32 IDDep = Convert.ToInt32(dr["F6"]);
                    //MyTrace("F5");
                    Decimal val_inv = Decimal.Zero;
                    if(dr["F5"].ToString().Trim()!=String.Empty)
                        val_inv = Convert.ToDecimal(dr["F5"]);
                    Decimal stoc_fap = stoc_fin;

                    Double stoc = Convert.ToDouble(stoc_fin);
                    if (stoc == 0)
                        continue;
                    DateTime datain = DateTime.Now;
                        
                        //Convert.ToDateTime(dbCont["Dum"]);
                    String cont = "3032"; //dbCont["Cont_sin"].ToString().Trim();
                    String cnt1 = String.Empty;//dbCont["Cont_an1"].ToString().Trim();
                    if (cnt1 != string.Empty)
                        cont += "." + cnt1;
                    //String cnt2 = dbCont["Cont_an2"].ToString().Trim();
                    //if (cnt2 != string.Empty && Convert.ToInt32(cnt2)!=0 )
                    //    cont += "." + cnt2;
                    Int16 codUM = 0;
                    
                    if (DenUM.ToUpper().Trim() == "BUC")
                        DenUM = "BUCATA";
                    if (DenUM.ToUpper().Trim() == "MP")
                        DenUM = "METRU PATRAT";
                    if (DenUM.ToUpper().Trim() == "ML")
                        DenUM = "METRU LINIAR";
                    int poz =0;
                    codUM = -1;
                    foreach (DataRowView drm in unitatiMasuraBindingSource)
                    {
                        if (drm["Denumire"].ToString() == DenUM)
                        {
                            codUM = Convert.ToInt16(drm["IDUnitateMasura"]);
                            break;
                        }
                    }
                    if (codUM == -1)
                    {
                        MyTrace("Unitate de masura inexistenta :"  +DenUM);
                        codUM = 0;
                    }
                    
                    Int32 IDDepart = 0;
                    poz = departamenteBindingSource.Find("Cod", IDDep.ToString());
                    if (poz != -1)
                    {
                        departamenteBindingSource.Position = poz;
                        DataRowView dum = ((DataRowView)departamenteBindingSource.Current);
                        IDDepart = Convert.ToInt32(dum["IDDepartament"]);
                    }
                    else 
                    {
                        MyTrace("Departament ineistent: " + IDDep.ToString());
                    }


                    Int32 stocf = Convert.ToInt32(stoc_fap);
                    
                    /*Int16 idx = Convert.ToInt16(dbCont["tip_obiect"]);
                    String categorie = "Nedefinit";
                    if (idx <= 15)
                        categorie = tipuri[idx - 1];*/


                    cmdInsert.CommandText = "INSERT INTO [AmixRegistru] ([NRInventar],[CantitateOI],[DataIntrare],[ContCheltuiala],[Denumire],[ValoareInventar],[StocFaptic],[IDGestionar],[CodUnitate],[InFolosinta],[Compus],[IDDepartament],[IDStare],[Categorie],[IDUnitateMasura]) VALUES ('" +
                        codn + "'," + stoc.ToString("F2", CultureInfo.InvariantCulture) + ",'" + datain.Year.ToString() + "-" + datain.Month.ToString() + "-" + datain.Day.ToString() + "','" + cont + "','" + den + "'," + val_inv.ToString("G", CultureInfo.InvariantCulture) + "," + stocf.ToString() + ",1," +
                        this.ModuleInfo.CodUnitate.ToString() + "," +(InFolosintaCB.Checked?"1":"0")  + "," + "3"+ "," + IDDepart.ToString() + ",5,''," +codUM.ToString() +  ")";
                    try
                    {
                        cmdInsert.ExecuteNonQuery();
                    }
                    catch (SqlException exs)
                    {
                        if (exs.Number == 2627)
                        {
                            //schimbanumarul de inventar
                            /*codn += DateTime.Now.Millisecond.ToString();
                            cmd.CommandText = "INSERT INTO [AmixRegistru] ([NRInventar],[CantitateOI],[DataIntrare],[ContCheltuiala],[Denumire],[ValoareInventar],[StocFaptic],[IDGestionar],[CodUnitate],[InFolosinta],[Compus],[IDDepartament],[IDStare],[Categorie]) VALUES ('" +
                                codn + "'," + stoc.ToString() + ",'" + datain.Year.ToString() + "-" + datain.Month.ToString() + "-" + datain.Day.ToString() + "','" + cont + "','" + den + "'," + val_inv.ToString("G", CultureInfo.InvariantCulture) + "," + stocf.ToString() + ",1," +
                                this.ModuleInfo.CodUnitate.ToString() + "," + (InFolosintaCB.Checked ? "1" : "0") + "," + (stoc == 1 ? "3" : "4") + "," + idNou.ToString() + ",5,'" + categorie + "'" + ")";
                            cmd.ExecuteNonQuery();*/
                            continue;
                        }
                        //throw exs;
                        MyTrace(cmdInsert.CommandText + ": " + exs.Message);
                        continue;
                    }
                    
                }

            }
            catch (Exception ex)
            {
                if (drs != null)
                {
                    int idx = bindingSource1.IndexOf(drs);
                    if (idx != -1)
                        bindingSource1.Position = idx;
                }
                MyTrace(ex.ToString());
            }
            finally
            {
                oledbConn.Close();
                oledbConn.Dispose();
                InchideConexiune(cmdInsert.Connection);
            }
            MessageBoxFrm mbt = new MessageBoxFrm("Mesaj", "Importul datelor s-a terminat!(" + bindingSource1.Count.ToString() + " inregistrari)", String.Empty, MessageBoxFrm.ImaginiMB.SeLucreazaIcon, null);
            mbt.AddButton("Iesire", 70, mbt.MessageBoxFrm_Click_Yes);
            mbt.ShowDialog();
        }

    }
}
